package com.wjh.www.dao;

import com.wjh.www.annotation.mybatis.Column;
import com.wjh.www.annotation.mybatis.Table;
import com.wjh.www.annotation.spring.Autowired;
import com.wjh.www.annotation.spring.Component;
import com.wjh.www.exception.DaoException;
import com.wjh.www.config.ConnectionPool;
import com.wjh.www.util.LoggerUtil;
import com.wjh.www.util.ReflectUtil;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;

/**
 * @author 26913
 */
@Component
public class SqlRunnerImpl implements SqlRunner {
    @Autowired
    public static ConnectionPool CONNECTION_POOL;
    Logger logger = LoggerUtil.getLogger();

    @Override
    public void fieldMapper(Object obj, List fieldNames, List fieldValues) {
        if (obj == null) {
            return;
        }
        List<Field> fields = ReflectUtil.getFields(obj);
        for (Field field : fields) {
            //获取属性名称
            String filedName = field.getName();
            //拼接getXxx方法（字符串名称）
            String methodName = "get" + filedName.substring(0, 1).toUpperCase() + filedName.substring(1);
            //生成obj所拥有的对应的method
            Object value;
            try {
                Method method = obj.getClass().getMethod(methodName);
                //通过反射执行getXxx方法
                value = method.invoke(obj);
            } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                e.printStackTrace();
                throw new DaoException("fieldMapper方法中映射字段发生异常");
            }
            if (value != null) {
                //取出该属性的名称和对应的值，映射成数据库字段名和值，填充进fieldNames和fieldValues
                fieldNames.add(field.getAnnotation(Column.class).value());
                fieldValues.add(value);
            }
        }
    }

    @Override
    public int insert(Object obj) {
        if (obj == null) {
            return 0;
        }
        //把传入的对象的非空的字段值映射成数据库表的字段和值
        List<Object> fieldNames = new ArrayList<>();
        List<Object> fieldValues = new ArrayList<>();
        fieldMapper(obj, fieldNames, fieldValues);
        //组建sql语句
        StringBuilder sql = new StringBuilder("INSERT INTO `" + obj.getClass().getAnnotation(Table.class).value() + "`(");
        for (Object fieldName : fieldNames) {
            sql.append(fieldName.toString()).append(",");
        }
        sql.setCharAt(sql.length() - 1, ')');
        sql.append(" VALUES(");
        for (int i = 0; i < fieldNames.size(); i++) {
            sql.append("?,");
        }
        sql.setCharAt(sql.length() - 1, ')');
        return executeUpdate(obj, null, sql.toString());
    }

    @Override
    public int delete(Object obj) {
        if (obj == null) {
            return 0;
        }
        //把传入的对象的非空的字段值映射成数据库表的字段和值
        List<Object> fieldNames = new ArrayList<>();
        List<Object> fieldValues = new ArrayList<>();
        fieldMapper(obj, fieldNames, fieldValues);
        //组建sql语句
        StringBuilder sql = new StringBuilder("DELETE FROM `" + obj.getClass().getAnnotation(Table.class).value() + "` WHERE ");
        for (Object fieldName : fieldNames) {
            sql.append(fieldName.toString()).append(" = ? AND ");
        }
        return executeUpdate(obj, null, sql.substring(0, sql.length() - 4));
    }

    @Override
    public int updateById(Object obj) {
        return updateByMap(obj, null);
    }

    @Override
    public int updateByMap(Object obj, Map<String, Object> map) {
        if (obj == null) {
            return 0;
        }

        //把传入的对象的非空的字段值映射成数据库表的字段和值并存进对应得集合
        List<Object> newFieldNames = new ArrayList<>();
        List<Object> newFieldValues = new ArrayList<>();
        fieldMapper(obj, newFieldNames, newFieldValues);
        //组建sql语句
        StringBuilder sql = new StringBuilder("UPDATE `" + obj.getClass().getAnnotation(Table.class).value() + "` SET ");
        for (Object newFieldName : newFieldNames) {
            sql.append(newFieldName.toString()).append(" = ?,");
        }
        sql.setCharAt(sql.length() - 1, ' ');

        if (map == null) {
            try {
                sql.append("WHERE id = ").append(obj.getClass().getDeclaredMethod("getId").invoke(obj));
            } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                throw new DaoException("update方法中的反射执行getId方法发生异常，对应obj的Class：" + obj.getClass(), e);
            }
        } else {
            sql.append(" WHERE ");
            for (String key : map.keySet()) {
                try {
                    Field field = obj.getClass().getDeclaredField(key);
                    Column column = field.getAnnotation(Column.class);
                    sql.append(column.value()).append(" = ").append(map.get(key)).append(" AND ");
                } catch (NoSuchFieldException e) {
                    logger.severe("实体类不存在“" + key + "”属性");
                    return 0;
                }
            }
            sql = new StringBuilder(sql.substring(0, sql.length() - 4));
        }

        return executeUpdate(obj, null, sql.toString());
    }

    @Override
    public int executeUpdate(Object obj, Object[] params, String sql) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //获取绑定在线程上面的连接（一个事务必须使用同一个connection），如果没有连接绑定在线程上面就直接从数据库获取连接
            conn = CONNECTION_POOL.getLocalConnection();
            if (conn == null) {
                conn = CONNECTION_POOL.getConnection();
            }
            ps = conn.prepareStatement(sql);
            //设置占位符
            if (obj != null) {
                setParams(obj, ps);
            }
            if (params != null) {
                setParams(ps, params);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            logger.severe("executeUpdate方法预编译sql语句异常：" + Objects.requireNonNull(ps));
            throw new DaoException("executeUpdate方法的预编译sql语句异常：" + ps, e);
        } finally {
            if (conn != CONNECTION_POOL.getLocalConnection()) {
                CONNECTION_POOL.returnConnection(conn);
            }
        }
    }

    @Override
    public Object executeQuery(String sql, Object[] params, Object obj) {
        Connection conn = null;
        try {
            conn = CONNECTION_POOL.getConnection();
            //如果没有参数不用预编译
            if (params == null && obj == null) {
                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(sql);
                if (rs.next()) {
                    return rs.getObject(1);
                }
            } else {
                PreparedStatement ps = conn.prepareStatement(sql);
                if (obj != null) {
                    setParams(obj, ps);
                } else {
                    setParams(ps, params);
                }
                //将ps填入参数后的完整语句赋值给sql
                sql = ps.toString();
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    return rs.getObject(1);
                }
            }
        } catch (SQLException e) {
            logger.severe("executeQuery方法预编译sql语句异常：" + sql);
            throw new DaoException("executeQuery方法的预编译sql语句异常：" + sql, e);
        } finally {
            CONNECTION_POOL.returnConnection(conn);
        }
        return null;
    }

    @Override
    public void setParams(Object obj, PreparedStatement ps) {
        //把传入的对象的非空的字段值映射成数据库表的字段和值
        List<?> fieldNames = new ArrayList<>();
        List<?> fieldValues = new ArrayList<>();
        fieldMapper(obj, fieldNames, fieldValues);
        Object[] params = fieldValues.toArray();
        for (int i = 0; i < params.length; i++) {
            try {
                ps.setObject(i + 1, params[i]);
            } catch (SQLException e) {
                logger.severe("sql设置占位符(对象)异常，对象：" + obj.toString());
                throw new DaoException("setParams(Object obj, PreparedStatement ps)方法异常：" + obj, e);
            }
        }
    }

    @Override
    public void setParams(PreparedStatement ps, Object[] params) {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                try {
                    ps.setObject(i + 1, params[i]);
                } catch (SQLException e) {
                    logger.severe("sql设置占位符（value）异常,参数：" + Arrays.toString(params));
                    throw new DaoException("setParams(PreparedStatement ps, Object[] params)方法异常：" + Arrays.toString(params), e);
                }
            }
        }
    }

    @Override
    public List<?> queryList(Object[] params, Object obj, Class<?> clazz, String sql) {
        Connection conn = null;
        PreparedStatement ps;
        List<Object> list = new LinkedList<>();
        try {
            //获取数据库连接(从连接池中取出即可)
            conn = CONNECTION_POOL.getConnection();
            ps = conn.prepareStatement(sql);
            //设置占位符
            if (obj != null) {
                setParams(obj, ps);
            }
            if (params != null && params.length != 0) {
                setParams(ps, params);
            }
            sql = ps.toString();
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            // 获取查询到数据的总行数
            int colCount = md.getColumnCount();
            //定义用于存放setXXX方法的数组
            String[] setters = new String[colCount];
            //存放查到的字段名的数组
            String[] columns = new String[colCount];
            String[] fieldNames = new String[colCount];
            for (int i = 0; i < colCount; i++) {
                columns[i] = md.getColumnLabel(i + 1);

                String[] split1 = md.getColumnLabel(i + 1).split("_");
                StringBuilder colName = new StringBuilder();
                for (String s : split1) {
                    colName.append(s.substring(0, 1).toUpperCase()).append(s.substring(1));
                }
                fieldNames[i] = colName.substring(0, 1).toLowerCase() + colName.substring(1);

                // 取得字段名并映射为setter方法名，忽略大小写,存在setters数组中
                String[] split2 = md.getColumnLabel(i + 1).split("_");
                StringBuilder setter = new StringBuilder("set");
                for (String s : split2) {
                    setter.append(s.substring(0, 1).toUpperCase()).append(s.substring(1));
                }
                setters[i] = setter.toString();
            }

            while (rs.next()) {
                // 被返回的一个对象
                Object returnObj = clazz.newInstance();
                for (int i = 0; i < colCount; i++) {
                    Field field = clazz.getDeclaredField(fieldNames[i]);
                    Method method = clazz.getMethod(setters[i], field.getType());
                    method.invoke(returnObj, rs.getObject(columns[i]));
                }
                list.add(returnObj);
            }

        } catch (SQLException | IllegalAccessException | InstantiationException | InvocationTargetException | NoSuchFieldException | NoSuchMethodException e) {
            logger.severe("queryList异常，params：" + Arrays.toString(Objects.requireNonNull(params)) + "  obj：" + Objects.requireNonNull(obj) + "  clazz:" + clazz + "  sql:" + sql);
            throw new DaoException("queryList方法在映射成对象时发生异常：" + e.getMessage(), e);
        } finally {
            CONNECTION_POOL.returnConnection(conn);
        }
        return list;
    }

    @Override
    public Object queryObject(Object[] params, Object obj, Class<?> clazz, String sql) {
        List<?> list = queryList(params, obj, clazz, sql);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public Object queryField(String sql, Object[] params, Object obj) {
        return executeQuery(sql, params, obj);
    }

    @Override
    public String getSelectSql(Object obj, String fields) {
        StringBuilder sql;
        //把传入的对象的非空的字段值映射成数据库表的字段和值
        List<Object> fieldNames = new ArrayList<>();
        List<Object> fieldValues = new ArrayList<>();
        fieldMapper(obj, fieldNames, fieldValues);
        // 组建sql语句（带where条件的）
        sql = new StringBuilder("SELECT " + fields + " FROM `" + obj.getClass().getAnnotation(Table.class).value() + "` WHERE ");
        for (Object fieldName : fieldNames) {
            sql.append(fieldName.toString()).append(" = ? AND ");
        }
        return sql.substring(0, sql.length() - 4);
    }

}
